
$SqlUserClass = New-PSClass SqlUser {

	note username ([string] $Args[0])
	note password ([string] $Args[1])

	Constructor {
	  $this.username = $Args[0]
	  $this.password = $Args[1]
	  if ( ($this.username -ne "") -and ($this.password -eq "") )
	  {
	    $this.Prompt()
	  }
	}

	method Prompt {
      $cred = Get-Credential $this.username
      $this.username = $cred.GetNetworkCredential().UserName
      $this.password = $cred.GetNetworkCredential().Password
	}	
} 

$PSSqlConnectionClass = New-PSClass  PSSqlConnection {

	note Connection 
	note -private ConnectionSuccess

	Constructor {
 	  $this.Connection = (New-Object "System.Data.SqlClient.SqlConnection")

		if ($Args[0] -ne $null)
    {
      $this.Connect($Args[0],$Args[1],$Args[2],$Args[3],$Args[4])
    }
  }

	method -private InternalConnect {
	
		if ($Args[0] -is [Array])
		{
			[string]$Server,[string]$Catalog,[string]$Username,[string]$Password,[string]$ConnectionExtras = $Args[0]
		}
		else
		{
			$Server = [string] $Args[0]
			$Catalog = [string] $Args[1]
			
			$Username = [string] $Args[2]
			$Password = [string] $Args[3]
			
			$ConnectionExtras = [string] $Args[4]
		}
		
		$user = $SqlUserClass.new($UserName, $Password)
		
		if ($user.username -ne "")
		{
			$this.Connection.ConnectionString = "Data Source=$Server;Initial Catalog=$Catalog;User ID=$($user.UserName);password=$($user.Password);$($ConnectionExtras)"
		}
		else
		{
			$this.Connection.ConnectionString = "Data Source=$Server;Initial Catalog=$Catalog;Integrated Security=SSPI;Persist Security Info=False;$($ConnectionExtras)"
		}

		try {
			$this.Connection.Open()
		} -catch {
			$msg = "Connection failed to Server: {0} Catalog: {1} User: {2}" -f $Server, $Catalog, $Username
			#Log-Error $msg
			Throw $msg
		}
	}
	method Connect {
		# This retry logic was necessary because somehow we would
		# periodically connect but fail on the first command
		$Cnt = 3
		while ($cnt -gt 0)
		{
			$cnt--
			$private.ConnectionSuccess =$TRUE
			
			$private.InternalConnect($Args[0],$Args[1],$Args[2],$Args[3],$Args[4])
			trap {
				if ($cnt -eq 0)
				{
					Throw $_
				}
				$private.ConnectionSuccess = $FALSE
				continue
			}
			
			$cmd = $PSSqlCommandClass.New($this)
			[void]	$cmd.ExecuteScalar("SELECT 1")
			if ($private.ConnectionSuccess)
			{
				return
			}
		}
	}
} 

$PSSqlCommandClass = New-PSClass PSSqlCommand {

	note -private PSSqlConnection 
	note -private QueriesRegex

	note Command
	note WhatIf
	
	Constructor {
		$private.PSSqlConnection = $Args[0]
		$this.Command = $private.PSSqlConnection.Connection.CreateCommand()

		$this.WhatIf = $Args[1]
		$regexOptions = "IgnorePatternWhitespace,Singleline,IgnoreCase,Multiline,Compiled"
		$QueriesExpression =@"
		(
			(?'Query'
				(?:							# Scan for a Comment or a single Char until GO line
					(?:
						/\*.*?\*/		# Search for '/*' Match until '*/'
					)|.           # or a single char
					)*?           # Match until a GO line
				)
			(?:^\s*go\s*$)    # Line with nothing but GO and spaces
		)*                  # As many as possible until last GO line
		(?'Query'.*)        # And grap the rest into Query group
"@

		$private.QueriesRegex = New-Object System.Text.RegularExpressions.Regex -ArgumentList ($QueriesExpression, [System.Text.RegularExpressions.RegexOptions]$regexOptions)
	}

	property Connection {
		return $private.PSSqlConnection;
	}
	
	method -private ParseSqlString {
		[string]$SqlArray = $Args[0]
		$query = New-Object "system.text.stringbuilder"

		$results = $private.QueriesRegex.Matches($SqlArray)
    foreach ($capture in $results[0].Groups["Query"].Captures)
		{
			$capture.Value
		}
	}
	
	method ParseSql {
		if ($Args[0] -is [array])
		{
			$sb = New-Object System.Text.StringBuilder
			$Args[0] | %{[void]$sb.AppendLine($_)}
			$private.ParseSqlString($sb.ToString())
		}
		else
		{
			$private.ParseSqlString($Args[0])
		}
	}

	method ExecuteNonQuery {
		$sql = $this.ParseSql( $Args[0] )
		foreach ($query in $sql)
		{
			Log-Message -level 'Verbose' $query
			Log-Message -level 'Verbose' "GO"
			if (! $this.WhatIf)
			{
				if ($query.Trim() -ne "")
				{
					Try {
						$this.Command.CommandText = $query
						[void] $this.Command.ExecuteNonQuery()
					} -Catch {
						$regex = New-Object System.Text.RegularExpressions.Regex "^ExecuteNonQuery:\s(?<message>.*)"
						$msg = $regex.match($_).groups["message"].value
						
						if ($msg -eq "") 
							{$msg = $_}
						
						Log-Error $msg
						Log-Warning "$query"
						Throw $msg
					}
				}
			}
		}
	}
	
	method AssureReady {
		
	}
	
	method ExecuteScalar {
		if ($Args[0])
		{
			$this.Command.CommandText = $Args[0]
		}

		$sb = New-Object "system.text.stringbuilder"
		log-Message -Level Verbose "EXECUTE SCALAR QUERY"
		
		if ($this.Command.CommandType -eq [System.Data.CommandType]::StoredProcedure)
		{
			$sb.Append( "EXECUTE " )
		}
		$sb.Append(  $this.Command.CommandText )
		foreach ($parm in $this.Command.Parameters)
		{
			$sb.Append( " $($parm.ParameterName)=$($parm.Value)")
		}
		
		Log-Message -Level Verbose $sb.ToString()

		if (! $this.WhatIf)
		{
			$this.Command.ExecuteScalar()
		}	
	}
	
	method ExecuteXmlQuery {
		$this.AssureReady()
		
		$sql = $Args[0]
		Try {
			$this.Command.CommandText = $sql
			$this.Command.ExecuteXmlReader()
		} -Catch {
			$regex = New-Object System.Text.RegularExpressions.Regex "^ExecuteXmlReader:\s(?<message>.*)"
			$msg = $regex.match($_).groups["message"].value
			if ($msg -eq "") {$msg = $_}
			Throw $msg
		}
	}
	
	method LoadAndExecuteXmlQuery {
		[System.Xml.XmlReader]$xrdr = $this.ExecuteXmlQuery($Args[0])
		[xml]$result = New-Object System.Xml.XmlDocument
		$result.Load($xrdr)
		$result
	}
} 


 
 